package de.uni.muenster.idw2.db;

import java.util.ArrayList;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import de.uni.muenster.idw2.data.BBBuilding;

public class DBHelper {
	
	public static final String BUILDING_DB = "dbOFM_IDW";
	public static final String BUILDING_TABLE = "OFM_IDW_buildings";
	
	private Activity act;
	private SQLiteDatabase DB;

	public DBHelper(Activity act){
		this.act = act;
		DB = openDB();
		createHighscoreTabel();
	}
	
	private SQLiteDatabase openDB(){
		SQLiteDatabase DB = act.openOrCreateDatabase(BUILDING_DB, act.MODE_PRIVATE, null);
		return DB;
	}
	
	private void createHighscoreTabel(){
		 DB.execSQL("CREATE TABLE IF NOT EXISTS " + BUILDING_TABLE 
				 + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				 		"name VARCHAR, " +
				 		"path VARCHAR, " +
				 		"lat DOUBLE, " +
				 		"lon DOUBLE," +
				 		"imgPath VARCHAR," +
				 		"description VARCHAR);");
	}
	
	public ArrayList<BBBuilding> getBuildings(){
		Cursor cursor = DB.rawQuery("SELECT * FROM " + BUILDING_TABLE, null);
		ArrayList<BBBuilding> buildings = new ArrayList<BBBuilding>();
		
		int namePointer = cursor.getColumnIndex("name");
		int pathPointer = cursor.getColumnIndex("path");
		int latPointer = cursor.getColumnIndex("lat");
		int lonPointer = cursor.getColumnIndex("lon");
		int imgPathPointer = cursor.getColumnIndex("imgPath");
		int descriptionPointer = cursor.getColumnIndex("description");
		
		String name;
		String path;
		double lat;
		double lon;
		String imgPath;
		String description;
		
		if(cursor.moveToFirst()){
			name = cursor.getString(namePointer);
			path = cursor.getString(pathPointer);
			lat = cursor.getDouble(latPointer);
			lon = cursor.getDouble(lonPointer);
			imgPath = cursor.getString(imgPathPointer);
			description = cursor.getString(descriptionPointer);
			
			BBBuilding bbBuilding = new BBBuilding(lon, lat, path, name, description, imgPath);
			buildings.add(bbBuilding);
			
			while(cursor.moveToNext()){
				name = cursor.getString(namePointer);
				path = cursor.getString(pathPointer);
				lat = cursor.getDouble(latPointer);
				lon = cursor.getDouble(lonPointer);
				imgPath = cursor.getString(imgPathPointer);
				description = cursor.getString(descriptionPointer);
				
				BBBuilding bbBuildingg = new BBBuilding(lon, lat, path, name, description, imgPath);
				buildings.add(bbBuildingg);
			}
		}
		
		return buildings;
	}
	
	public void addBuilding(BBBuilding bbBuilding){
		
		Cursor cursor = DB.rawQuery("SELECT * FROM " + BUILDING_TABLE + " WHERE path= '" + bbBuilding.getPath() + "'", null);
		if(!cursor.moveToFirst()){
			DB.execSQL("INSERT INTO " + BUILDING_TABLE 
					+ "(name, path, lat, lon, imgPath, description)"  + "VALUES (" +
					"'" + bbBuilding.getName() + "', " +
					"'" + bbBuilding.getPath() + "', " +
					"" + bbBuilding.getLat() + ", " +
					"" + bbBuilding.getLon() + ", " +
					"'" + bbBuilding.getPathIMG() + "', " +
					"'" + bbBuilding.getDescription() + "');");
		}
	}
	
	public void closeDB(){
		DB.close();
	}
	
	public void deleteDB(){
		act.deleteDatabase(BUILDING_DB);
		Log.v("wichtig", "deleteDB");
	}

}
